﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using WW.NActiveRecord.Internal.Utils;

namespace WW.NActiveRecord.ConnectionAdapters.SqlServer{
    /// <summary>
    /// 提供对SqlServer数据库访问的一个安全封装
    /// 以保证连接/事务代码的严格执行,
    /// 开发中请不要自行建立连接等对象而应全部依赖于此工具
    /// </summary>
    public class SqlDb {
        private readonly string connectionString = null;

        /// <summary>
        /// 目前只支持通过数据库串创建
        /// </summary>
        /// <param name="connectionUrl"></param>
        public SqlDb(string connectionUrl) {
            this.connectionString = connectionUrl;
        }

        /// <summary>
        /// 数据库连接串
        /// </summary>
        public string ConnectionString {
            get { return connectionString; }
        }

        /// <summary>
        /// 根据一个sql字符串执行查询
        /// </summary>
        /// <param name="sql">无参数的sql字符串</param>
        /// <returns></returns>
        public DataTable ExecuteQuery(string sql) {
            return ExecuteQuery(new SqlCommand(sql));
        }


        /// <summary>
        /// 根据一个command执行查询,sqlCommand只须装配CommandText和Parameters
        /// 而无需配置Connection
        /// </summary>
        /// <param name="sqlCommand">待查询的command</param>
        /// <returns></returns>
        public DataTable ExecuteQuery(SqlCommand sqlCommand) {
            return ExecuteInConnection(delegate(SqlConnection connection) {
                                           sqlCommand.Connection = connection;
                                           using (SqlDataAdapter adp = new SqlDataAdapter(sqlCommand)) {
                                               DataTable table = new DataTable();
                                               adp.Fill(table);
                                               return table;
                                           }
                                       });
        }


        public object ExecuteScalar(string sql) {
            return ExecuteScalar(new SqlCommand(sql));
        }

        public object ExecuteScalar(SqlCommand sqlcommand) {
            return ExecuteInConnection(delegate(SqlConnection connection) {
                                           sqlcommand.Connection = connection;
                                           return sqlcommand.ExecuteScalar();
                                       });
        }
        /// <summary>
        /// 执行一次更新式查询:INSERT/UPDATE/DELETE ...
        /// </summary>
        /// <param name="sqlCommand">更新语句command</param>
        /// <returns>受影响的行数,等同于SqlCommand#ExecuteNonQuery()的结果</returns>
        public int ExecuteNoQuery(SqlCommand sqlCommand) {
            return ExecuteInConnection(delegate(SqlConnection connection) {
                                           sqlCommand.Connection = connection;
                                           return sqlCommand.ExecuteNonQuery();
                                       });
        }

        public int ExecuteNoQuery(string command) {
            return ExecuteNoQuery(new SqlCommand(command));
        }

        /// <summary>
        /// 打开一个Connection对象后执行参数action,并在action执行完毕后关闭连接
        /// </summary>
        /// <param name="callback">callback</param>
        public void ExecuteInConnection(Action<SqlConnection> callback) {
            //不使用返回值，任意定义一个范型返回值参数DBNull
            ExecuteInConnection<DBNull>(delegate(SqlConnection connection) {
                                            callback(connection);
                                            return null;
                                        });
        }

        /// <summary>
        /// 打开一个Connection对象后执行参数callback,并在callback执行完毕后关闭连接
        /// </summary>
        /// <typeparam name="TResult">传入callback的返回类型</typeparam>
        /// <param name="callback">callback</param>
        /// <returns>返回调用者callback参数的返回值</returns>
        public TResult ExecuteInConnection<TResult>(Func<SqlConnection, TResult> callback) {
            using (SqlConnection connection = new SqlConnection(connectionString)) {
                connection.Open();
                return callback(connection);
            }
        }


        private static void ExecuteSqlTransaction(string connectionString) {
            using (SqlConnection connection = new SqlConnection(connectionString)) {
                connection.Open();

                SqlCommand command = connection.CreateCommand();
                SqlTransaction transaction;

                // Start a local transaction.
                transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);

                // Must assign both transaction object and connection
                // to Command object for a pending local transaction
                command.Connection = connection;
                command.Transaction = transaction;

                try {
                    command.CommandText =
                        "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
                    command.ExecuteNonQuery();
                    command.CommandText =
                        "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
                    command.ExecuteNonQuery();
                    transaction.Commit();
                    Console.WriteLine("Both records are written to database.");
                } catch (Exception e) {
                    try {
                        transaction.Rollback();
                    } catch (SqlException ex) {
                        if (transaction.Connection != null) {
                            Console.WriteLine("An exception of type " + ex.GetType() +
                                              " was encountered while attempting to roll back the transaction.");
                        }
                    }

                    Console.WriteLine("An exception of type " + e.GetType() +
                                      " was encountered while inserting the data.");
                    Console.WriteLine("Neither record was written to database.");
                }
            }
        }
        /// <summary>
        /// 运行一大段标准的sql script文本
        /// 由于GO语句不能在ado.net环境下运行,此方法把GO语句作为分割Command运行的标志
        /// 如脚本:
        /// 
        /// USE master
        /// GO
        /// SET NOCOUNT ON
        /// GO
        /// 
        /// 以上script分割为2个Command分别运行:
        /// 1.USE master
        /// 2.SET NOCOUNT ON
        /// </summary>
        /// <param name="script"></param>
        public void RunScript(string script) {
            this.ExecuteInConnection(delegate(SqlConnection connection) {
                SqlCommand command = connection.CreateCommand();

                Action<string> executeAction = delegate(string sql) {
                    try {
                        command.CommandText = sql;

                        command.ExecuteNonQuery();
                    } catch (Exception e) {
                        StringBuilder exception = new StringBuilder();
                        exception.AppendLine("\n######################### Execute Script Error! ############################");
                        exception.AppendLine(sql);
                        exception.AppendLine(  "############################################################################");
                        throw new Exception(exception.ToString(), e);
                    }
                };

                using (StringReader scriptReader = new StringReader(script)) {
                    StringBuilder sb = new StringBuilder();
                    for (string line = null; (line = scriptReader.ReadLine()) != null; ) {
                        //"GO" 不能运行,遇到GO后把之前读取的文本执行
                        if (line.Trim().ToUpper().StartsWith("GO")) {
                            executeAction(sb.ToString());
                            sb = new StringBuilder();
                            continue;
                        }
                        sb.AppendLine(line);
                    }
                    if (sb.Length != 0) {
                        executeAction(sb.ToString());
                    }
                }

            });

        }

        /// <summary>
        /// 给列名添加分隔符
        /// </summary>
        /// <param name="names"></param>
        /// <returns>用中括号扩起列名集合:  [[columnName1] ,[columnName2]]</returns>
        public IEnumerable<string> QuoteColumnNames(IEnumerable<string> names) {
            return names.Select<string, string>(name => QuoteColumnName(name));
        }

        /// <summary>
        /// 给列名添加分隔符 
        /// </summary>
        /// <param name="name"></param>
        /// <returns>用中括号扩起列名:  [columnName]</returns>
        public string QuoteColumnName(string name) {
            return  "[" + name + "]";
        }

        /// <summary>
        /// 给表名添加分隔符
        /// </summary>
        /// <param name="name"></param>
        /// <returns>用中括号扩起表名:  [tableName]</returns>
        public string QuoteTableName(string name)
        {
            return  QuoteColumnName(name);
        }

        ///TODO 全面使用quote？
        //private string QuoteColumnName(string columnName){
        //    return string.Format("{0}{1}{2}", QuoteIdentifierLeft, columnName, QuoteIdentifierLeft);
        //}
        //private string QuoteTableName(string tableName){
        //    return string.Format("{0}{1}{2}", QuoteIdentifierLeft, tableName, QuoteIdentifierRight);
        //}

        //internal string QuoteIdentifierLeft{
        //    get { return "["; }
        //}

        //internal string QuoteIdentifierRight{
        //    get { return "]"; }
        //}

        /// <summary>
        ///替换所有的@xx为参数值,
        ///select * form table where id=1比
        ///select * form table where id=@id更易读些
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        internal static String Format(SqlCommand command)
        {
            String result = command.CommandText;

            for (int i = 0; i < command.Parameters.Count; i++)
            {
                SqlParameter param = command.Parameters[i];
                string parameterName = param.ParameterName.StartsWith("@") ? param.ParameterName : "@" + param.ParameterName;
                string paramValue = QuoteParameterValue(param.Value);
                result = result.Replace(parameterName, paramValue);
            }
            return result;
        }
        internal static string QuoteParameterValue(object paramValue)
        {
            if (paramValue == null)
            {
                return "NULL";
            }
            else if (paramValue is bool || paramValue is string || paramValue is char)
            {
                return string.Format("'{0}'", paramValue);
            }
            else if (paramValue is DateTime)
            {
                return string.Format("'{0}'",
                                     ((DateTime)paramValue).ToString("yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture));
            }
            else if (paramValue is Guid)
            {
                return string.Format("'{0}'", paramValue.ToString().ToUpperInvariant());
            }
            else if (paramValue is byte[])
            {
                return string.Format("0x{0}", ((byte[])paramValue).Select(x => x.ToString("X2")).JoinString(""));
            }
            else if (paramValue is sbyte[])
            {
                return string.Format("0x{0}", ((sbyte[])paramValue).Select(x => x.ToString("X2")).JoinString(""));
            }
            else if (typeof(ICollection).IsAssignableFrom(paramValue.GetType()))
            {
                ICollection c= (ICollection) paramValue;
                List<string> s=new List<string>();
                foreach (var o in c){
                    s.Add(QuoteParameterValue(o));
                }
                return s.JoinString();
            }
            else if (paramValue is byte || paramValue is sbyte ||
                     paramValue is short || paramValue is ushort ||
                     paramValue is int || paramValue is uint ||
                     paramValue is long || paramValue is ulong ||
                     paramValue is float || paramValue is double ||
                     paramValue is decimal)
            {
                return paramValue.ToString();
            }
            else
            {
                return string.Format("'{0}'", paramValue);
            }

        }

    }
}